package com.hz.framework.dao;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import com.hz.framework.ajax.BPComDwr;
import com.hz.framework.util.CharacterDispose;


public class CommonDBDao extends DBDAO {
	
	private List lstValue = null;
	/* 组成SQL */
	private StringBuilder sbSQL = null;
	
	/* 组成表列 */
	private StringBuilder sbColumn = null;
	
	/* 组成VALUES ? */
	private StringBuilder sbValue = null;
	
	/* 设置 strSeparatorColumns 数组长度 */
	private int iLength = 0;
	
	private boolean isFinished = false;
	
	/**
	 * @author 韩飞
	 * @time May 19, 2011	
	 * @param sql
	 * @return
	 * @throws Exception 
	 * 方法功能: 根据sql语句JDBC查询数据单一结果 以map键值对形成一条数据，用list集合装载
	 * 			并将对应的数据列名称(键的名称) 形成 , 分隔的字符串
	 * 			用map装载 数据集合list 和 键值的字符串stmt
	 */
	public Map jsonListmap(String sql)throws Exception{
		return jsonListmap(sql,"");
	}
	public Map jsonListmap(String sql,String talog)throws Exception{
		DBQ db=new DBQ();
    	Map maps=new HashMap();
    	String stmt="";
    	lstValue = new ArrayList();
        try{
        	if(!"".equals(talog)){
    			db.setCatalog(talog);
    		}
	        ResultSet rs = db.getResults(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        while(rs.next()){
	            Map map = new HashMap();
	          for(int i=1;i<=  rsmd.getColumnCount();i++){
	              String value = (String) rs.getString(i);
	              if("null".equalsIgnoreCase(value))value="";
	              map.put(rsmd.getColumnLabel(i),value);
	              stmt+=rsmd.getColumnLabel(i)+",";
	          }
	          lstValue.add(map);
	        }
        }catch (Exception ex){
        	System.out.println(" Map jsonListmap 方法异常");
		}finally{
			db.close();
		}
        maps.put("list", lstValue);
        maps.put("stmt", stmt);
        return maps;
    }
	/**
	 * @author 韩飞
	 * @time May 19, 2011	
	 * @param sql
	 * @return 
	 * 方法功能:  根据sql语句JDBC查询数据单一结果 以map键值对形成一条数据，用list集合装载返回
	 */
	public List DBjsonList(String sql){
		return DBjsonList(sql,"");
	}
	public List DBjsonList(String sql,String talog){
		DBQ db=new DBQ();
		lstValue = new ArrayList();
        ResultSet rs;
		try {
			if(!"".equals(talog)){
				db.setCatalog(talog);
			}
			rs = db.getResults(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        while(rs.next()){
	            Map map = new HashMap();
	          for(int i=1;i<=  rsmd.getColumnCount();i++){
	              String value = (String) rs.getString(i);
	              if("null".equalsIgnoreCase(value))value="";
	              map.put(rsmd.getColumnLabel(i),value);
	          }
	          lstValue.add(map);
	        }
		} catch (SQLException e) {
			System.out.println(" List DBjsonList 方法异常");
			//e.printStackTrace();
		}finally{
			db.close();
		}
        return lstValue;
    }
	/**
	 * @author 韩飞
	 * @time Jan 10, 2012	
	 * @param sql
	 * @param flag
	 * @return 
	 * 方法功能: flag为true情况下，对sql语句进行解密，然后进行查询
	 */
	public static List staticList(String sql,boolean flag){
		if(flag){
			System.out.println("++"+sql);
			sql = BPComDwr.doDesString(sql);
		}
		DBQ db=new DBQ();
		List lstValue = new ArrayList();
        ResultSet rs;
		try {
			rs = db.getResults(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        while(rs.next()){
	            Map map = new HashMap();
	          for(int i=1;i<=  rsmd.getColumnCount();i++){
	              String value = (String) rs.getString(i);
	              if("null".equalsIgnoreCase(value))value="";
	              map.put(rsmd.getColumnLabel(i),value);
	          }
	          lstValue.add(map);
	        }
		} catch (SQLException e) {
			System.out.println(" List DBjsonList 方法异常");
			//e.printStackTrace();
		}finally{
			db.close();
		}
		return lstValue;
	}
	/**
	 * @author 韩飞
	 * @time May 19, 2011	
	 * @param sql
	 * @return 
	 * 方法功能: 根据sql语句JDBC查询数据单一结果 以map键值对形式返回数据
	 */
	public Map DBjsonMap(String sql){
		return DBjsonMap(sql,"");
	}
	public Map DBjsonMap(String sql,String talog){
		DBQ db=new DBQ();
        Map map = new HashMap();
        ResultSet rs;
		try {
			if(!"".equals(talog)){
				db.setCatalog(talog);
			}
			rs = db.getResults(sql);
	        ResultSetMetaData rsmd = rs.getMetaData();
	        if(rs.next()){
	          for(int i=1;i<=  rsmd.getColumnCount();i++){
	              String value = (String) rs.getString(i);
	              if("null".equalsIgnoreCase(value))value="";
	              map.put(rsmd.getColumnLabel(i),value);
	          }
	        }
		} catch (SQLException e) {
			System.out.println(" Map DBjsonMap 方法异常");
			e.printStackTrace();
		}finally{
			db.close();
		}
        return map;
    }
//	更新表字段值  参数 :  表名 , 要更新的字段 , 对应的值 , id条件的值
	public boolean updateTable(String table, String upwhat, String upvalue, String whereid, String wherevalue){
		boolean isFinished = false;
		String sql = "";
		if (table == null||table == "")
			return isFinished;
			
		sql = "update "+table+" set "+upwhat+"='"+upvalue+"' where "+whereid+"='" + wherevalue + "'";
		DBQ db = new DBQ();
		try {
			isFinished = db.execute(sql);
		} catch (Exception exception) {
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	jdbc更新数据库操作  （value：以字段为键的map对象，names：对应字段名称数组，table：对应数据库表名,主键名称）
	public boolean doUpdate(Map value, String[] names, String table,String idvalue) throws Exception {

		/* 组成SQL */
		sbSQL = new StringBuilder();
		
		/* 组成表列 */
		sbColumn = new StringBuilder();
		
		/* 组成VALUES ? */
		sbValue = new StringBuilder();
			
		if(names == null){
			return false;
		}else{
			iLength = names.length;
		}
		/**
		 * 生成 INSERT SQL及获取控件对象值
		 * */
		if(value != null){
			
			sbColumn.append("UPDATE ");
			sbColumn.append(table);
			sbColumn.append(" SET ");
			for(int j=0;j<iLength;j++){
				if(!idvalue.equals(names[j])){
					sbColumn.append(names[j]+"='"+value.get(names[j])+"'");
					if(j != iLength -1){
						sbColumn.append(",");
					}
				}
			}
			sbValue.append(" where "+idvalue+"='"+value.get(idvalue)+"'");
			sbSQL.append(sbColumn);
			sbSQL.append(sbValue);
			System.out.println(sbSQL);
			
		}
		/* 执行INSERTSQL */
		DBQ db = new DBQ();
		try {
			isFinished = db.execute(sbSQL.toString());
		} catch (Exception exception) {
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	组成jdbc 更新语句 （value：以字段为键的map对象，names：对应字段名称数组，table：对应数据库表名,主键名称）
	public String doUpdateSQL(Map value, String[] names, String table,String idvalue) throws Exception {

		/* 组成SQL */
		sbSQL = new StringBuilder();
		
		/* 组成表列 */
		sbColumn = new StringBuilder();
		
		/* 组成VALUES ? */
		sbValue = new StringBuilder();
			
		if(names == null){
			return "";
		}else{
			iLength = names.length;
		}
		/**
		 * 生成 INSERT SQL及获取控件对象值
		 * */
		if(value != null){
			
			sbColumn.append("UPDATE ");
			sbColumn.append(table);
			sbColumn.append(" SET ");
			for(int j=0;j<iLength;j++){
				if(!idvalue.equals(names[j])){
					sbColumn.append(names[j]+"='"+value.get(names[j])+"'");
					if(j != iLength -1){
						sbColumn.append(",");
					}
				}
			}
			sbValue.append(" where "+idvalue+"='"+value.get(idvalue)+"'");
			sbSQL.append(sbColumn);
			sbSQL.append(sbValue);
			System.out.println(sbSQL);
			
		}
		return sbSQL.toString();
	}
	
	//jdbc插入数据库操作  （value：以字段为键的map对象，names：对应字段名称数组，table：对应数据库表名）
	public boolean doInsert(Map value, String[] names, String table) throws Exception {

		/* 组成SQL */
		sbSQL = new StringBuilder();
		
		/* 组成表列 */
		sbColumn = new StringBuilder();
		
		/* 组成VALUES ? */
		sbValue = new StringBuilder();
			
		if(names == null){
			return false;
		}else{
			iLength = names.length;
		}
		/**
		 * 生成 INSERT SQL及获取控件对象值
		 * */
		if(value != null){
			
			sbColumn.append("INSERT INTO ");
			sbColumn.append(table);
			sbColumn.append(" (");
			sbValue.append(" VALUES('");
			for(int j=0;j<iLength;j++){
				sbColumn.append(names[j]);
				sbValue.append(value.get(names[j]));
				if(j != iLength -1){
					sbColumn.append(",");
					sbValue.append("','");			
				}
			}

			sbValue.append("')");
			sbColumn.append(")");
			sbSQL.append(sbColumn);
			sbSQL.append(sbValue);
			System.out.println(sbSQL);
			
		}
		/* 执行INSERTSQL */
		DBQ db = new DBQ();
		try {
			isFinished = db.execute(sbSQL.toString());
		} catch (Exception exception) {
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	组成jdbc 插入语句  （value：以字段为键的map对象，names：对应字段名称数组，table：对应数据库表名）
	public String doInsertSQL(Map value, String[] names, String table) throws Exception {

		/* 组成SQL */
		sbSQL = new StringBuilder();
		
		/* 组成表列 */
		sbColumn = new StringBuilder();
		
		/* 组成VALUES ? */
		sbValue = new StringBuilder();
			
		if(names == null){
			return "";
		}else{
			iLength = names.length;
		}
		/**
		 * 生成 INSERT SQL及获取控件对象值
		 * */
		if(value != null){
			
			sbColumn.append("INSERT INTO ");
			sbColumn.append(table);
			sbColumn.append(" (");
			sbValue.append(" VALUES('");
			for(int j=0;j<iLength;j++){
				sbColumn.append(names[j]);
				sbValue.append(value.get(names[j]));
				if(j != iLength -1){
					sbColumn.append(",");
					sbValue.append("','");			
				}
			}

			sbValue.append("')");
			sbColumn.append(")");
			sbSQL.append(sbColumn);
			sbSQL.append(sbValue);
//			System.out.println(sbSQL);
		}
		return sbSQL.toString();
	}
	/**
	 * @author 韩飞
	 * @time May 19, 2011	
	 * @param table
	 * @param where
	 * @param value
	 * @return 
	 * 方法功能: 执行一个集合的删除功能 参数: 表名 , where条件(单一), 对应的值的集合
	 */
	public boolean DeleteSql(String table, String where, String value) {
		
		if (!"".equals(table)) {
			/* 执行DELETESQL */
			String sql = "delete from " + table ;
			if(!"".equals(where)){
				sql += " where " + where + " in (" + value + ")";
			}
			DBQ db = new DBQ();
			try {
				db.setAutoCommit(false);//设置为不自动提交
				isFinished = db.executeCreate(sql);
				db.commit();//提交事务
			} catch (Exception exception) {
				db.rollback();
				isFinished = false;
			} finally {
				db.close();
			}
		}
		return isFinished;
	}
//	普通sql语句 执行对库操作方法
	public boolean insorupd(String sql){
		return insorupd(sql,"");
	}
	public boolean insorupd(String sql,String talog){
		isFinished = false;
		DBQ db = new DBQ();
		try {
			if(!"".equals(talog)){
				db.setCatalog(talog);
			}
			db.setAutoCommit(false);//设置为不自动提交
			isFinished = db.executeCreate(sql);
			db.commit();//提交事务
		} catch (Exception exception) {
			db.rollback();
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	预处理 执行对库操作方法
	public boolean insorupd(String sql,String[] values){
		isFinished = false;
		DBQ db = new DBQ();
		try {
			db.setAutoCommit(false);//设置为不自动提交
			isFinished = db.operatedb(sql, values);
			db.commit();//提交事务
		} catch (Exception exception) {
			db.rollback();
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	执行对库操作 sql语句的数组
	public boolean insorupdArray(String[] sql){
		isFinished = false;
		DBQ db = new DBQ();
		try {
			db.setAutoCommit(false);//设置为不自动提交
			if(sql.length>0){
				for(int i=0;i<sql.length;i++){
					isFinished = db.executeCreate(sql[i]);
				}
			}
			db.commit();//提交事务
		} catch (Exception exception) {
			db.rollback();
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
	//执行对库操作 sql语句的list集合
	public boolean insorupdArray(List sql){
		isFinished = false;
		DBQ db = new DBQ();
		try {
			db.setAutoCommit(false);//设置为不自动提交
			if(!sql.isEmpty()){
				for(int i=0;i<sql.size();i++){
					isFinished = db.executeCreate((String)sql.get(i));
				}
			}
			db.commit();//提交事务
		} catch (Exception exception) {
			db.rollback();
			isFinished = false;
		} finally {
			db.close();
		}
		return isFinished;
	}
//	普通sql语句 执行对库操作方法 , 返回刚插入的数据的主键
	public String insorupdrid(String sql){
		String id = "0";
		DBQ db = new DBQ();
		try {
			db.setAutoCommit(false);//设置为不自动提交
			id = db.updateRkey(sql);
			db.commit();//提交事务
		} catch (Exception exception) {
			db.rollback();
			id = "-1";
		} finally {
			db.close();
		}
		return id;
	}
	/**
	 * @author 冯森
	 * @time 2011-4-19	
	 * @param sql
	 * @return 
	 * 方法功能:执行sql返回一列 一个字符串
	 */
	public String getOneValue(String sql){
		String value = "";
		DBQ db = new DBQ();
		try {
			ResultSet rs = db.query(sql);
			if(rs.next()){
				value=rs.getString(1);
			}
		} catch (Exception exception) {
			System.out.println(exception);
		} finally {
			db.close();
		}
		return value;
	}
	
}
